# db_openser.pm
# Copyright (C) 2006, 2007  Stephane Alnet
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; either version 3
# of the License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program.  If not, see <http://www.gnu.org/licenses/>.
# 

#
# For more information: http://carrierclass.net/
#
use strict; use warnings;

package CCNProxy::Provision::Database::openser;
use base qw(CCNCore::Provision::CCNDatabase);

use CCNProxy::Provision::Application::openser;
our %avps = %CCNProxy::Provision::Application::openser::avps;

use CCN::Text;
use CCNCore::Scripting;
use CCN::Scripting::sql;
use CCNCore::Home;
use Digest::MD5 qw(md5_hex);

sub model_name() { return 'openser' }
sub get_db_names()
{
    my $self = shift;

    return unless defined $self->p->{db_member} and $self->p->system_pk == $self->p->primary_db_server;

    return $self->sql_get_array(q(SELECT 'db_px_'||id FROM px_clusters WHERE db_cluster = ?),$self->p->db_member);
    # If no record was found it means we are not the proper db_cluster.
}

# Database table creations and domain insertions are done once per database cluster
# (since they get replicated).

# YYY permissions.so complains about /etc/ser/permissions.(allow|deny)

sub bootstrap_env()
{
    my $forever = '2020-05-28 21:32:15';

    return {
        DEFAULT_ALIASES_EXPIRES     => $forever,
        DEFAULT_Q                   => '1.0',
        DEFAULT_CALLID              => 'Default-Call-ID',
        DEFAULT_CSEQ                => '42',
        DEFAULT_LOCATION_EXPIRES    => $forever,
    };
}

sub grants($)
{
    our $self = shift;
    our $c    = shift;

    # TBD All of this should be rewritten as an independent script (instead of a script-writing exercise).
    our $SALT = $self->system_salt;

    $self->sql_do_for( <<'SQL', $self->p->db_member,
        SELECT id FROM px_clusters WHERE db_cluster = ?
SQL
    sub
    {
        our $PXY_CLUSTER = shift;
        our $DBNAME      = 'db_px_'.$PXY_CLUSTER;

        my $GRANTS = '';

        my $PXY_CLUSTER_name = $self->p->make_cluster_name($PXY_CLUSTER,'px');

        $self->sql_do_for( <<'SQL', $PXY_CLUSTER,
            SELECT pk FROM server WHERE px_member = ?
SQL
        sub
        {
            my $CLIENT_pk = shift;
            my $CLIENT_name = $self$self->p->make_server_name($CLIENT_pk);
            # proxy/intf
            my $CLIENT_IP = $self->get_realtime_ip_of($CLIENT_pk);

            # See program_ser.pm
            my $PASSWORD = $self->make_password($self->p->{db_member},${SALT},${CLIENT_pk},'openser');

            $GRANTS .= <<"SQL";
GRANT ALL PRIVILEGES ON ${DBNAME}.* TO openser\@${CLIENT_IP} IDENTIFIED BY '${PASSWORD}';
SQL
        });

        foreach(CCNCore::Home::config."/local/bin/grants-openser-${PXY_CLUSTER}.sql")
        {
            print_to $c->root().$_, $GRANTS;
            $c->commit_file($_);

            $self->log(ref($self)."::grants: run_command $DBNAME $_\n"),
            CCN::Scripting::run_command(CCN::Scripting::sql::SQL_FILE(${DBNAME}, $_))
                if $self->has_changed(${DBNAME}) or $c->changed($_);
        }

    });

}

sub overwrite()
{
    our $self = shift;
    our $c    = shift;

    # Database table creations and domain insertions are done once per database cluster
    # (since they get replicated).
    return unless defined $self->p->{db_member} and $self->p->system_pk == $self->p->primary_db_server;

    # YYY permissions.so complains about /etc/ser/permissions.(allow|deny)

    # TBD All of this should be rewritten as an independent script (instead of a script-writing exercise).

    our $SALT = $self->system_salt;

    $self->sql_do_for( <<'SQL', $self->p->{db_member},
        SELECT id FROM proxy_cluster WHERE db_cluster = ?
SQL
    sub
    {
        our $PXY_CLUSTER = shift;
        our $DBNAME      = 'db_px_'.$PXY_CLUSTER;

        my $do_domain_table = sub
        {
            my $DOMAINS = '';

            my $PXY_CLUSTER_name = $self->p->make_cluster_name($PXY_CLUSTER,'px');

            $self->sql_do_for( <<'SQL', $PXY_CLUSTER,
            SELECT id FROM server WHERE px_member = ?
SQL
            sub
            {
                my $CLIENT_pk = shift;
                my $CLIENT_name = $self$self->p->make_server_name($CLIENT_pk);
                # proxy/intf
                my $CLIENT_IP = $self->get_realtime_ip_of($CLIENT_pk);
                $DOMAINS .= <<"SQL";
DELETE FROM domain WHERE domain = '${PXY_CLUSTER_name}';
INSERT INTO domain(domain) VALUES ('${PXY_CLUSTER_name}');
DELETE FROM domain WHERE domain = '${CLIENT_IP}';
INSERT INTO domain(domain) VALUES ('${CLIENT_IP}');
DELETE FROM domain WHERE domain = '${CLIENT_name}';
INSERT INTO domain(domain) VALUES ('${CLIENT_name}');
SQL

            });

            $self->sql_do_for( <<'SQL', $PXY_CLUSTER,
            SELECT dns_name FROM domain WHERE proxy_cluster = ?
SQL
            sub
            {
                my $dns_name = shift;
                $DOMAINS .= <<"SQL";
DELETE FROM domain WHERE domain = '${dns_name}';
INSERT INTO domain(domain) VALUES ('${dns_name}');
SQL

            });

            foreach(CCNCore::Home::config."/local/bin/proxy-domains-${PXY_CLUSTER}.sql")
            {
                print_to $c->root().$_, $DOMAINS;
                $c->commit_file($_);
                CCN::Scripting::run_command(CCN::Scripting::sql::SQL_FILE(${DBNAME}, $_))
                    if $self->has_changed(${DBNAME}) or $c->changed($_);
            }

        };
        $do_domain_table->();

        # This was previously in db_trunks.
        my $proxy_trusted = sub
        {

            my $proxy_trusted = '';

            $self->sql_do_for( <<'SQL', $PXY_CLUSTER,
                SELECT source FROM inbound_trunk WHERE the_domain IN (SELECT id FROM domain WHERE px_cluster = ?)
SQL
            sub
            {
                my $ip = shift;
                $proxy_trusted .= <<"SQL";
DELETE FROM trusted WHERE src_ip = '${ip}';
INSERT INTO trusted(src_ip,proto,from_pattern) VALUES ('${ip}','any','^sip:.*\$');
SQL
            });

        };
        $proxy_trusted->();


        SELECT host FROM customer WHERE host IS NOT NULL AND the_domain IN (SELECT id FROM domain WHERE px_cluster = ?)

        my $do_trunks = sub
        {
            # This was previously in db_trunks.

            # TBD Need to split in smaller chunks for retail / managed.
            # (Not an issue for hosted / centralized because the number of proxies will be low.)

            $self->sql_do_for( <<'SQL', $self->p->system_pk, $PXY_CLUSTER,
                SELECT pk, cp_cluster, gw_ip, gw_port, server FROM trunk WHERE system = ? AND px_cluster = ?
SQL
            sub
            {
                my $TRUNK_pk    = shift;
                my $cp_cluster  = shift;
                my $gateway_ip  = shift;
                my $gateway_port= shift;
                my $server      = shift;

                my $TRUNK_id = CCNv2::Parameters::trunk_id($TRUNK_pk);

                # YYY Can I actually put a port number here? Isn't SER going to reject it
                # (since I'm (only) replacing the domain part in the AVP processing)?

                my $trunk_target;
                my $trunk_ip;

                my $proxy_conf = '';
                my $trusted = '';

                # Asterisk-based trunk
                if( defined $server )
                {
                    $trunk_ip = $self->get_realtime_ip_of($server);
                    # Force direct access
                    $trunk_target .= "$trunk_ip:5062";
                }
                else
                {
                    # Force SRV resolution
                    $trunk_target .= $self->p->make_cluster_name($cp_cluster,'cpc').':0';
                }

                # Trunk servers and gateways are trusted by default.
                $trusted .= <<"SQL" if defined $trunk_ip;
DELETE FROM trusted WHERE src_ip = '${trunk_ip}';
INSERT INTO trusted(src_ip,proto,from_pattern) VALUES ('${trunk_ip}','any','^sip:.*\$');
SQL
                $trusted .= <<"SQL" if defined $gateway_ip;
DELETE FROM trusted WHERE src_ip = '${gateway_ip}';
INSERT INTO trusted(src_ip,proto,from_pattern) VALUES ('${gateway_ip}','any','^sip:.*\$');
SQL

                # target=I:67 (for phones)
                # cos=I:72 (for phones)
                # srcip  =I:71 (tmp)

                # trunk  =I:68 (TTr${TRUNK} to SRV)
                # gateway=I:69 (IP to FTr${TRUNK})
                # gwcp   =I:70 (IP to SRV for FTr)
                # tgw    =I:73 (tgw${TRUNK} to IP)

                # SER needs avpops i:68 that maps "$TRUNK" to a target (for "TTr$TRUNK")
                $proxy_conf .= <<"SQL";
--- SER \$trunk (I:68)
DELETE FROM avpops WHERE uuid = 'TTr${TRUNK_id}';
INSERT INTO avpops (uuid,username,domain,attribute,type,value)
VALUES ('TTr${TRUNK_id}','TTr${TRUNK_id}', '', '68', '2', '${trunk_target}');
SQL

                if( defined $gateway_ip )
                {
                    my $cpc_name = $self->p->make_cluster_name(${cp_cluster},'cpc');
                    my $gw_name = $self->p->make_cluster_name("gw${TRUNK_pk}",'gw');

                    # Third-party gateway
                    my $gateway_target =  "$gateway_ip";
                    $gateway_target .= ":$gateway_port" if $gateway_port != 5060;
                    # Third-party gateway
                    # Route to call-processing servers.
                    # This needs avpops i:69 that maps an SRC_IP to a "$TRUNK" (for "FTr$TRUNK")
                    # This needs avpops i:70 that maps an SRC_IP to a SRV (for "FTr$TRUNK")
                    $proxy_conf .= <<SQL;
DELETE FROM avpops WHERE uuid = '${gateway_ip}';
--- SER gateway (I:69)
INSERT INTO avpops (uuid,username,domain,attribute,type,value)
VALUES ('${gateway_ip}', '${gateway_ip}', '', '69', '2', 'FTr${TRUNK_id}');
--- SER gwcp (I:70)
INSERT INTO avpops (uuid,username,domain,attribute,type,value)
VALUES ('${gateway_ip}', '${gateway_ip}', '', '70', '2', '${cpc_name}');

DELETE FROM avpops WHERE uuid = 'TGw${TRUNK_id}';
--- SER tgw (I:73)
INSERT INTO avpops (uuid,username,domain,attribute,type,value)
VALUES ('TGw${TRUNK_id}', '', 'TGw${TRUNK_id}', '73', '2', '${gateway_target}');
--- SER tgwredir (I:76)
INSERT INTO avpops (uuid,username,domain,attribute,type,value)
VALUES ('TGw${TRUNK_id}', '', 'TGw${TRUNK_id}', '76', '2', '${gw_name}');

DELETE FROM domain WHERE domain = '${gw_name}';
INSERT INTO domain (domain) VALUES ('${gw_name}');
SQL
                    # was: 
                    # was $gateway_target, but doesn't work well for Level3 recognition since
                    # $src_ip nor $from/domain have a port if the default is used, and $gateway uses $tgw :(

                    # Route to specific IP (call-processing flow-around).
                    # map $digits to $ip
                    $self->sql_do_for(<<'SQL',${TRUNK_pk},
                        SELECT "number", ip, port FROM proxy_route WHERE trunk = ?
SQL
                    sub
                    {
                        my ($number,$target,$port) = @_;
                        # "aliases" has FTr$TRUNKn$NUMBER@$SRV mapped to a target on an individual gateway
                        my $username = "FTr${TRUNK_id}m${number}";
                        my $domain   = ${cpc_name};
                        my $contact  = "sip:${number}\@${target}:${port}";
                        $proxy_conf .= <<"SQL";
DELETE FROM aliases WHERE username = '$username' AND domain = '$domain' AND contact = '$contact';
INSERT INTO aliases (username,domain,contact) VALUES ('$username', '$domain','$contact');
SQL
                    });

                    # Serialized failover
                    my $rank = 0;
                    $self->sql_do_for(<<'SQL',${TRUNK_pk},
                        SELECT gw_ip, gw_port FROM trunk_failover WHERE trunk = ? ORDER BY rank ASC
SQL
                    sub
                    {
                        my $next_gateway_ip = shift;
                        my $next_gateway_port = shift;
                        my $next_gateway_target = $next_gateway_ip;
                        $next_gateway_target .= ":$next_gateway_port" if $next_gateway_port != 5060;
                        my $prev = "ADv${TRUNK_id}r${rank}";
                        $prev = "TGw${TRUNK_id}" if $rank == 0;
                        $rank++;
                        my $next = "ADv${TRUNK_id}r${rank}";
                        $proxy_conf .= <<SQL;
--- SER gwadv (I:77)
DELETE FROM avpops WHERE uuid = '${prev}' AND attribute = '77';
INSERT INTO avpops (uuid,username,domain,attribute,type,value)
VALUES ('${prev}', '', ',${prev}', '80', '2', '${next}');
--- SER tgw (I:73)
DELETE FROM avpops WHERE uuid = '${next}' AND attribute = '73';
INSERT INTO avpops (uuid,username,domain,attribute,type,value)
VALUES ('${next}', '', '${next}', '73', '2', '${next_gateway_target}');
SQL
                    });

                }

                # TBD This is not optimal. But we won't need to do this anymore once this all becomes an independent script.
                foreach(CCNCore::Home::config."/local/bin/proxy-trunk-${PXY_CLUSTER}-${TRUNK_pk}.sql")
                {
                    print_to $c->root().$_, $trusted . $proxy_conf;
                    $c->commit_file($_);
                    CCN::Scripting::run_command(CCN::Scripting::sql::SQL_FILE(${DBNAME}, $_))
                        if $self->has_changed(${DBNAME}) or $c->changed($_);
                }

            }); # for @trunks
            
        };
        $do_trunks->();

        my $do_lines = sub
        {
            my $proxy_conf = '';

            $self->sql_do_for( <<'SQL', $PXY_CLUSTER,
                SELECT "line".cos, "line".login, "line"."password", pool.enterprise, enterprise.salt, phone.pool, "line"."number" 
                FROM "line", phone, pool, enterprise 
                WHERE "line".phone = phone.pk AND enterprise.pk = pool.enterprise
                 AND phone.pool IN (SELECT pk FROM pool WHERE px_cluster = ?);
SQL
            sub
            {
                my ($COS,$USERNAME,$PASSWORD,$ENTERPRISE,$SALT,$POOL,$NUMBER) = @_;

                my $USE_CHALLENGE = $CCNv2::Provision::ser_parameters::USE_CHALLENGE;

                my @domains = ('');
                # What do we use to build the password?
                # my $PASSWORD = substr($self->make_password(${ENTERPRISE},${SALT},${USERNAME},'phone'),12,12);
                my $HA1 = md5_hex("${USERNAME}:${USE_CHALLENGE}:${PASSWORD}");

                $proxy_conf .= <<SQL;
                DELETE FROM subscriber WHERE username = '${USERNAME}';
                DELETE FROM avpops WHERE username = '${USERNAME}';
                DELETE FROM aliases WHERE username = '${ENTERPRISE}m$NUMBER';
SQL

                for my $domain ( @domains )
                {
                    my $HA1B = md5_hex("${USERNAME}\@${domain}:${USE_CHALLENGE}:${PASSWORD}");

                    my $cp_name = $self->p->make_cluster_name(${POOL},'cp');

                    # Generate the SER config for this phone
                    $proxy_conf .= <<SQL;
--- Remove REGISTER info
DELETE FROM subscriber WHERE username = '${USERNAME}' AND domain = '${domain}';
--- Remove all AVPs
DELETE FROM avpops WHERE username = '${USERNAME}' AND domain = '${domain}';

--- User authentication
INSERT INTO subscriber (username,domain,password,ha1,ha1b)
VALUES ('${USERNAME}', '${domain}', '${PASSWORD}', '${HA1}', '${HA1B}');

--- Login to call-processing cluster SRV
INSERT INTO avpops (username,domain,attribute,type,value)
VALUES ('${USERNAME}', '${domain}', '67', '2', '${cp_name}');
--- Login to Enterprise ID
INSERT INTO avpops (username,domain,attribute,type,value)
VALUES ('${USERNAME}', '${domain}', '80', '2', '${ENTERPRISE}');
--- Login to Pool ID
INSERT INTO avpops (username,domain,attribute,type,value)
VALUES ('${USERNAME}', '${domain}', '81', '2', '${POOL}');
--- Login to CoS ID
INSERT INTO avpops (username,domain,attribute,type,value)
VALUES ('${USERNAME}', '${domain}', '82', '2', '${COS}');
--- Login to Extension
INSERT INTO avpops (username,domain,attribute,type,value)
VALUES ('${USERNAME}', '${domain}', '83', '2', '${NUMBER}');
SQL
                } # for @realms/@domains

            }); # for @lines

            foreach(CCNCore::Home::config."/local/bin/proxy-do_lines-${PXY_CLUSTER}.sql")
            {
                print_to $c->root().$_, $proxy_conf;
                $c->commit_file($_);
                CCN::Scripting::run_command(CCN::Scripting::sql::SQL_FILE(${DBNAME}, $_))
                    if $self->has_changed(${DBNAME}) or $c->changed($_);
            }

    
        };
        $do_lines->();

        my $do_lines_routing = sub
        {
            # We need to do these for every single phone in the enterprise, otherwise we won't be able to locate the phones for these.
            # (this is the part that could use an HLR!)
            # Note: this will scale better than having this configured in Asterisk, though.

            # So we list all the enterprises in all the pools that use this proxy cluster.
            my $proxy_conf = '';

            $self->sql_do_for( <<'SQL', $PXY_CLUSTER,
                SELECT "line"."number", "line".login, pool.enterprise, pool.px_cluster, pool.cp_cluster FROM "line", phone, pool
                WHERE "line".phone = phone.pk AND phone.pool = pool.pk
                  AND pool.pk IN
                    (SELECT DISTINCT pk FROM pool WHERE enterprise IN 
                        (SELECT DISTINCT enterprise FROM pool WHERE px_cluster = ?));
SQL
            sub
            {
                my ($NUMBER,$USERNAME,$ENTERPRISE_pk,$TARGET_PX,$TARGET_CP) = @_;
                my $ENTERPRISE_id = CCNv2::Parameters::enterprise_id($ENTERPRISE_pk);

                my $PX_NAME = $self->p->make_cluster_name($TARGET_PX,'px');
                # TBD 'cpc' or 'cp' ?
                my $CP_NAME = $self->p->make_cluster_name($TARGET_CP,'cpc');

                my $domain = '';

                my $username = "${ENTERPRISE_id}m${NUMBER}";
                my $phonename = "PHo${ENTERPRISE_id}m${NUMBER}";

                $proxy_conf .= <<SQL;
-- Delete all existing aliases
DELETE FROM aliases WHERE username = '${username}' AND domain = '${domain}';
DELETE FROM aliases WHERE username = '${phonename}' AND domain = '${domain}';
SQL

                $proxy_conf .= <<SQL;
-- Number (extension) to phones (line appearances)
INSERT INTO aliases (username,domain,contact)
VALUES ('${username}','${domain}','sip:${USERNAME}\@$PX_NAME');
SQL

                if( $TARGET_PX = $PXY_CLUSTER )
                {
                    $proxy_conf .= <<SQL;
-- Number (extension) to processing for local numbers
INSERT INTO aliases (username,domain,contact)
VALUES ('${phonename}','${domain}','sip:PHo${ENTERPRISE_id}m${NUMBER}\@$CP_NAME');
SQL
                }
                else
                {
                    $proxy_conf .= <<SQL;
-- Number (extension) to processing for non-local numbers
INSERT INTO aliases (username,domain,contact)
VALUES ('${phonename}','${domain}','sip:PHo${ENTERPRISE_id}m${NUMBER}\@$PX_NAME');
SQL
                }
            });

            foreach(CCNCore::Home::config."/local/bin/proxy-do_lines_routing-${PXY_CLUSTER}.sql")
            {
                print_to $c->root().$_, $proxy_conf;
                $c->commit_file($_);
                CCN::Scripting::run_command(CCN::Scripting::sql::SQL_FILE(${DBNAME}, $_))
                    if $self->has_changed(${DBNAME}) or $c->changed($_);
            }

        };
        $do_lines_routing->();


        $self->database_done($DBNAME);

    }); # foreach @proxy_clusters

}

sub _db_cluster_for
{
    my $self = shift;
    my $domain = shift;
    return $self->sql_get(<<'SQL',$domain);
        SELECT db_cluster FROM px_cluster
        WHERE id = (SELECT px_cluster FROM domain WHERE id = ?)
SQL
}


1;
